//使用mysql.createPool
const mysql = require('mysql')
const config = {
    host: 'localhost',
    port: '3306',
    user: 'test',
    password: 'test',
    database: 'test'
}

function buildsql(name=''){
    this.sFields = "SELECT *";
    this.sWhere = "";
    this.sOrder = "";
    this.sLimit = "";    
    this.table = name;		
    this.sTable=" FROM "+ this.table +" "; 
    this.pool = mysql.createPool(config)
    this.query = function(sql,values,callback){  
        this.pool.getConnection(function(err,connection){ 
            //if (err) throw err;
            if (err) console.log(err.message);
            connection.query(sql,values,function(err,results){  
                if (err) console.log(err.message);
            // console.log(results)
                callback(err, JSON.parse(JSON.stringify(results))) // 结果回调
                connection.release() // 释放连接资源 | 跟 connection.destroy() 不同，它是销毁
            })
        })  
    }
    this.select=function(values,callback){  
        let sql = this.selsql();//         
        return this.query(sql,values,callback);
    };
    this.insert = function(values,callback){ 
        let sql = 'INSERT INTO '+ this.table + ' SET ?'
        return this.query(sql,values,callback); 
    }
    this.update = function(values,callback){ 
        let sql = 'UPDATE '+ this.table + ' SET ? ' + this.sWhere
        return this.query(sql,values,callback);
    }
    this.remove = function(values,callback){ 
        let sql = 'DELETE FROM '+ this.table + this.sWhere 
        return this.query(sql,values,callback);
    }
}
buildsql.prototype.table=function(table_name){
    this.table = table_name;		
    this.sTable=" FROM "+ this.table +" ";
    return this;	
}
buildsql.prototype.field=function(name = '*'){		
    this.sFields = "SELECT "+ name;
    return this;	
}
buildsql.prototype.where=function(name){		
    this.sWhere = " WHERE "+ name;
    return this;	
}
buildsql.prototype.order=function(name){	
    if(name.match('/.*?(ASC|DESC)\s*?/i'))this.sOrder = " ORDER BY "+ name;
    else this.sOrder=" ORDER BY "+ name + " ASC";
    return this;	
}
buildsql.prototype.limit=function(val,ext=''){		
    this.sLimit =" LIMIT "+ val + (ext? "," + ext : '');		
    return this;	
}
buildsql.prototype.selsql=function(count=false){
    if(count)return "SELECT COUNT(*) AS total " + this.sTable + this.sWhere;		
    return this.sFields + this.sTable + this.sWhere + this.sOrder + this.sLimit;
}

module.exports = buildsql